package org.timeismoney.payment.web;

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.timeismoney.payment.common.Md5Util;
import org.timeismoney.payment.model.SchoolYear;
import org.timeismoney.payment.model.Student;
import org.timeismoney.payment.model.Unit;
import org.timeismoney.payment.model.StudentDTO;

public class ExcelUtil {

	@SuppressWarnings("deprecation")
	public static HSSFWorkbook generateTemplate() {
		HSSFWorkbook workbook = new HSSFWorkbook();
		String sheetName = "导入学生";

		HSSFSheet sheet = workbook.createSheet(sheetName);

		int totalColumns = 3;

		String[] titleArray = new String[totalColumns];
		titleArray[0] = "学号";
		titleArray[1] = "学生姓名";
		titleArray[2] = "初始密码(为空将使用学号作密码)";

		sheet.setColumnWidth((short) 0, (short) 5000);
		sheet.setColumnWidth((short) 1, (short) 7000);
		sheet.setColumnWidth((short) 2, (short) 7000);
		// Set title style
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setBorderLeft((short) 1);
		titleStyle.setBorderRight((short) 1);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		// Set title value
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell titleCell = row.createCell((short) 0);
		titleCell.setCellStyle(titleStyle);
		titleCell.setCellValue("学生导入模板(注意：请核实是否有学号重复的记录)");

		int initRow = 0;
		int initColumn = 0;
		int endRow = 0;
		int endColumn = totalColumns - 1;
		sheet.addMergedRegion(new Region(initRow, (short) (initColumn), endRow,
				(short) (endColumn)));

		setColumnTitles(sheet, titleArray, titleStyle);
		return workbook;
	}
	
	//Add by chamdenjo@gmail.com
	@SuppressWarnings("deprecation")
	public static HSSFWorkbook generateUnitTemplate() {
		HSSFWorkbook workbook = new HSSFWorkbook();
		String sheetName = "院系模板";
		HSSFSheet sheet = workbook.createSheet(sheetName);

		int totalColumns = 8;

		String[] titleArray = new String[totalColumns];
		titleArray[0] = "院系编号";
		titleArray[1] = "院系";
		titleArray[2] = "专业编号";
		titleArray[3] = "专业";
		titleArray[4] = "级编号";
		titleArray[5] = "级";
		titleArray[6] = "班编号";
		titleArray[7] = "班";

		sheet.setColumnWidth((short) 0, (short) 4000);
		sheet.setColumnWidth((short) 1, (short) 4000);
		sheet.setColumnWidth((short) 2, (short) 4000);
		sheet.setColumnWidth((short) 3, (short) 4000);
		sheet.setColumnWidth((short) 4, (short) 4000);
		sheet.setColumnWidth((short) 5, (short) 4000);
		sheet.setColumnWidth((short) 6, (short) 4000);
		sheet.setColumnWidth((short) 7, (short) 4000);
		// Set title style
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setBorderLeft((short) 1);
		titleStyle.setBorderRight((short) 1);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		// Set title value
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell titleCell = row.createCell((short) 0);
		titleCell.setCellStyle(titleStyle);
		titleCell.setCellValue("院系模板(注意：请不要修改此模板，以免数据丢失)");

		int initRow = 0;
		int initColumn = 0;
		int endRow = 0;
		int endColumn = totalColumns - 1;
		sheet.addMergedRegion(new Region(initRow, (short) (initColumn), endRow,
				(short) (endColumn)));

		setColumnTitles(sheet, titleArray, titleStyle);
		return workbook;
	}
	
	//Add by chamdenjo@gmail.com
		@SuppressWarnings("deprecation")
		public static HSSFWorkbook generateAllUnit(List<Unit> leafUnits) {
			HSSFWorkbook workbook = new HSSFWorkbook();
			String sheetName = "所有院系结构数据";

			HSSFSheet sheet = workbook.createSheet(sheetName);

			int totalColumns = 8;

			String[] titleArray = new String[totalColumns];
			titleArray[0] = "院系编号";
			titleArray[1] = "院系";
			titleArray[2] = "专业编号";
			titleArray[3] = "专业";
			titleArray[4] = "级编号";
			titleArray[5] = "级";
			titleArray[6] = "班编号";
			titleArray[7] = "班";

			sheet.setColumnWidth((short) 0, (short) 4000);
			sheet.setColumnWidth((short) 1, (short) 4000);
			sheet.setColumnWidth((short) 2, (short) 4000);
			sheet.setColumnWidth((short) 3, (short) 4000);
			sheet.setColumnWidth((short) 4, (short) 4000);
			sheet.setColumnWidth((short) 5, (short) 4000);
			sheet.setColumnWidth((short) 6, (short) 4000);
			sheet.setColumnWidth((short) 7, (short) 4000);
			// Set title style
			HSSFCellStyle titleStyle = workbook.createCellStyle();
			titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
			titleStyle.setBorderLeft((short) 1);
			titleStyle.setBorderRight((short) 1);
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
			titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			// Set title value
			HSSFRow row = sheet.createRow((short) 0);
			HSSFCell titleCell = row.createCell((short) 0);
			titleCell.setCellStyle(titleStyle);
			titleCell.setCellValue("院系模板(注意：)");

			int initRow = 0;
			int initColumn = 0;
			int endRow = 0;
			int endColumn = totalColumns - 1;
			sheet.addMergedRegion(new Region(initRow, (short) (initColumn), endRow,
					(short) (endColumn)));

			setColumnTitles(sheet, titleArray, titleStyle);
			
			if (leafUnits != null){
				for (int rowNo = 0; rowNo < leafUnits.size(); rowNo++) {
					Unit unit = leafUnits.get(rowNo);
					int cols = titleArray.length;
					titleArray[cols-2] = String.valueOf(unit.getUnitNo());
					titleArray[cols-1] =unit.getUnitName();
					cols -=2;
					Unit parentUnit = unit.getParentUnit();
					while (parentUnit != null){
						if (cols >=2 ){
							titleArray[cols-2] =String.valueOf(parentUnit.getUnitNo());
							titleArray[cols-1] =parentUnit.getUnitName();
							cols -=2;
							parentUnit = parentUnit.getParentUnit();
						}
					}
					HSSFRow varRow = sheet.createRow((short) (rowNo + 2));
					for (int k = 0; k < titleArray.length; k+=2) {
						HSSFCell[] cell = new HSSFCell[titleArray.length];
						cell[k] = varRow.createCell((short) k);
						cell[k].setCellValue(titleArray[k]);
						cell[k+1] = varRow.createCell((short) k+1);
						cell[k+1].setCellValue(titleArray[k+1]);
					}
				}
			}
			return workbook;
		}
		
@SuppressWarnings("deprecation")
public static HSSFWorkbook generateTemplateWithUnitNo() {
	HSSFWorkbook workbook = new HSSFWorkbook();
	String sheetName = "导入学生";
		HSSFSheet sheet = workbook.createSheet(sheetName);

		int totalColumns = 4;

		String[] titleArray = new String[totalColumns];
		titleArray[0] = "学号";
		titleArray[1] = "学生姓名";
		titleArray[2] = "初始密码(为空将使用学号作密码)";
		titleArray[3] = "班级#";

		sheet.setColumnWidth((short) 0, (short) 5000);
		sheet.setColumnWidth((short) 1, (short) 7000);
		sheet.setColumnWidth((short) 2, (short) 7000);
		sheet.setColumnWidth((short) 3, (short) 5000);
		// Set title style
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setBorderLeft((short) 1);
		titleStyle.setBorderRight((short) 1);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		// Set title value
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell titleCell = row.createCell((short) 0);
		titleCell.setCellStyle(titleStyle);
		titleCell.setCellValue("学生导入模板(注意：请核实是否有学号重复的记录)");

		int initRow = 0;
		int initColumn = 0;
		int endRow = 0;
		int endColumn = totalColumns - 1;
		sheet.addMergedRegion(new Region(initRow, (short) (initColumn), endRow,
				(short) (endColumn)));

		setColumnTitles(sheet, titleArray, titleStyle);
		return workbook;
	}
	
	@SuppressWarnings("deprecation")
	public static HSSFWorkbook generateStudentsReport(List<Student> stus, Unit unit) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		String sheetName = unit.getUnitName();

		HSSFSheet sheet = workbook.createSheet(sheetName);

		int totalColumns = 4;

		String[] titleArray = new String[totalColumns];
		titleArray[0] = "学号";
		titleArray[1] = "学生姓名";
		titleArray[2] = "初始密码(为空将使用学号作密码)";
		titleArray[3] = "班级#";

		sheet.setColumnWidth((short) 0, (short) 5000);
		sheet.setColumnWidth((short) 1, (short) 7000);
		sheet.setColumnWidth((short) 2, (short) 7000);
		sheet.setColumnWidth((short) 3, (short) 5000);
		// Set title style
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setBorderLeft((short) 1);
		titleStyle.setBorderRight((short) 1);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		// Set title value
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell titleCell = row.createCell((short) 0);
		titleCell.setCellStyle(titleStyle);
		titleCell.setCellValue(unit.getUnitName()+"-学生列表");

		int initRow = 0;
		int initColumn = 0;
		int endRow = 0;
		int endColumn = totalColumns - 1;
		sheet.addMergedRegion(new Region(initRow, (short) (initColumn), endRow,
				(short) (endColumn)));

		setColumnTitles(sheet, titleArray, titleStyle);
		for (int rowNo = 0; rowNo < stus.size(); rowNo++) {
			HSSFRow varRow = sheet.createRow((short) (rowNo + 2));
			Student stu = stus.get(rowNo);
			HSSFCell[] dataCell = new HSSFCell[totalColumns];
			dataCell[0] = varRow.createCell(0);
			dataCell[0].setCellValue(stu.getStuNo());
			
			dataCell[1] = varRow.createCell(1);
			dataCell[1].setCellValue(stu.getStuName());
			
			dataCell[2] = varRow.createCell(2);
			dataCell[2].setCellValue("");
			
			dataCell[3] = varRow.createCell(3);
			dataCell[3].setCellValue(stu.getUnit().getUnitNo());
		}
		return workbook;
	}

	@SuppressWarnings("deprecation")
	private static void setColumnTitles(HSSFSheet sheet, String[] titleArray,
			HSSFCellStyle titleStyle) {
		HSSFRow row;
		row = sheet.createRow((short) 1);
		for (int k = 0; k < titleArray.length; k++) {
			HSSFCell[] cell = new HSSFCell[titleArray.length];
			cell[k] = row.createCell((short) k);
			cell[k].setCellStyle(titleStyle);
			cell[k].setCellType(HSSFCell.CELL_TYPE_STRING);
			cell[k].setCellValue(titleArray[k]);
		}
	}

	@SuppressWarnings("deprecation")
	public static HSSFWorkbook generateReport(List report,SchoolYear schoolYear) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		String sheetName = "学生报表";

		HSSFSheet sheet = workbook.createSheet(sheetName);
		// Set title style
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setBorderLeft((short) 1);
		titleStyle.setBorderRight((short) 1);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell titleCell = row.createCell((short) 0);
		titleCell.setCellStyle(titleStyle);
		titleCell.setCellValue(schoolYear.getSchoolYearName() + " 学生缴费表");

		int initRow = 0;
		int initColumn = 0;
		int endRow = 0;
		int endColumn = 1;
		
		Map firstRow = null;
		if(report.size()>0&&report.get(0)!=null){
			firstRow = (Map) report.get(0);
		}
		if(firstRow==null){
			sheet.addMergedRegion(new Region(initRow, (short) (initColumn), endRow,
					(short) (endColumn)));
			return workbook;
		}

		List<String> titles = new ArrayList<String>();
		titles.add("学号");
		titles.add("学生姓名");
		sheet.setColumnWidth((short) 0, (short) 5000);
		sheet.setColumnWidth((short) 1, (short) 3000);
		for(Iterator i = firstRow.keySet().iterator();i.hasNext();){
			String colName = (String) i.next();
			if(!titles.contains(colName)&&!"stu_no".equals(colName)&&!"stu_name".equals(colName)){
				titles.add(colName);
				sheet.setColumnWidth((short) (titles.size()-1), (short) 4000);
			}
		}
		endColumn = titles.size();
		sheet.addMergedRegion(new Region(initRow, (short) (initColumn), endRow,
				(short) (endColumn)));
		//total
		titles.add("缴费合计");
		sheet.setColumnWidth((short) (titles.size()-1), (short) 7000);
		
		setColumnTitles(sheet, titles.toArray(new String[0]), titleStyle);
		for (int rowNo = 0; rowNo < report.size(); rowNo++) {
			HSSFRow varRow = sheet.createRow((short) (rowNo + 2));
			Map rowMap = (Map) report.get(rowNo);
			HSSFCell[] dataCell = new HSSFCell[titles.size()];
			BigDecimal rowTotal = new BigDecimal(0);
			for (int i = 0; i < dataCell.length; i++) {
				Object value = rowMap.get(titles.get(i));
				dataCell[i] = varRow.createCell(i);
				if(i==0){
					dataCell[i].setCellValue(rowMap.get("stu_no").toString());
				}else if(i==1){
					dataCell[i].setCellValue(rowMap.get("stu_name").toString());
				}else if(i==dataCell.length-1){
					dataCell[i].setCellValue(rowTotal.doubleValue());
				}else{
					BigDecimal amt = BigDecimal.valueOf(Double.valueOf(value.toString()));
					dataCell[i].setCellValue(amt.doubleValue());
					rowTotal=rowTotal.add(amt);
				}
			}
		}
		
		return workbook;
	}

	@SuppressWarnings("deprecation")
	public static List<Student> readExcel(InputStream is) {
		int titleRow = 0;
		int totalRow = 0;
		HSSFSheet sheet = null;
		try {
			HSSFWorkbook workbook = new HSSFWorkbook(is);
			sheet = workbook.getSheetAt(0);
			totalRow = sheet.getLastRowNum() + 1;
			if (totalRow > 1000) {
				return null;
			}
			// Get title row
			for (int k = 0; k < totalRow; k++) {
				HSSFRow row = sheet.getRow(k);
				if (row != null) {
					HSSFCell cell0 = row.getCell((short) 0);
					if (cell0 != null
							&& "学号".equals(cell0.getStringCellValue())) {
						titleRow = k;
						break;
					}
				}
			}
		} catch (Exception e) {
			return null;
		}

		List<Student> uploadBatchList = new ArrayList<Student>();

		if (titleRow > 0) {
			for (int k = (titleRow + 1); k < totalRow; k++) {
				HSSFRow rowCell = sheet.getRow(k);
				if (rowCell != null) {
					int colNumber = 0;
					String stuNo = null;
					int columnStuNo = colNumber++;
					try {
						stuNo = (String) verifyObj(
								rowCell.getCell((short) columnStuNo), "s"); // stuNo
					} catch (Exception e) {
						stuNo = "";
					}

					String stuName = null;
					int columnStuName = colNumber++;
					try {
						stuName = (String) verifyObj(
								rowCell.getCell((short) columnStuName), "s"); // stuName
					} catch (Exception e) {
						stuName = "";
					}
					String pwd = "";
					int columnPwd = colNumber++;
					try {
						pwd = (String) verifyObj(
								rowCell.getCell((short) columnPwd), "s"); // pwd
					} catch (Exception e) {
						pwd = stuNo;
					}
					pwd = Md5Util.MD5(pwd);
					
					Integer unitNo = null;
					int columnUnitNo = colNumber++;
					try {
						unitNo = Integer.valueOf(verifyObj(
								rowCell.getCell((short) columnUnitNo), "s").toString()); // unit#
					} catch (Exception e) {
						
					}

					// check duplication in excel
					boolean flag = true;
					if ("".equals(stuNo) || "".equals(stuName)
							|| "合计".equals(stuNo)) {
						continue;
					} else {
						for (int j = 0; j < uploadBatchList.size(); j++) {
							Student batch = uploadBatchList.get(j);
							if (stuNo.equals(batch.getStuNo())) {
								flag = false;
								break;
							}
						}
					}
					if (flag) {
						StudentDTO stuBatch = new StudentDTO();
						stuBatch.setStuNo(stuNo);
						stuBatch.setStuName(stuName);
						stuBatch.setPwd(pwd);
						stuBatch.setExcelLineNo(k+1);
						if(unitNo!=null){
							Unit u = new Unit();
							u.setUnitNo(unitNo);
							stuBatch.setUnit(u);
						}
						uploadBatchList.add(stuBatch);
					}
				}
			}
		}
		return uploadBatchList;
	}
	
	//add by chamdenjo@gmail.com
	@SuppressWarnings("deprecation")
	public static List<String[]> readExcelForUnit(InputStream is) {
		int titleRow = 0;
		int totalRow = 0;
		HSSFSheet sheet = null;
		try {
			HSSFWorkbook workbook = new HSSFWorkbook(is);
			sheet = workbook.getSheetAt(0);
			totalRow = sheet.getLastRowNum() + 1;
			if (totalRow > 1000) {
				return null;
			}
			// Get title row
			for (int k = 0; k < totalRow; k++) {
				HSSFRow row = sheet.getRow(k);
				if (row != null) {
					HSSFCell cell0 = row.getCell((short) 0);
					if (cell0 != null
							&& "院系编号".equals(cell0.getStringCellValue())) {
						titleRow = k;
						break;
					}
				}
			}
		} catch (Exception e) {
			return null;
		}

		List<String[]> uploadBatchList = new ArrayList<String[]>();

		if (titleRow > 0) {
			for (int k = (titleRow + 1); k < totalRow; k++) {
				HSSFRow rowCell = sheet.getRow(k);
				if (rowCell != null) {
					int colNumber = 0;
					String level1 = null;
					int columnStuNo = ++colNumber;
					try {
						level1 = (String) verifyObj(
								rowCell.getCell((short) columnStuNo), "s"); // level1
					} catch (Exception e) {
						break;
					}

					String level2 = null;
					colNumber++;
					int columnStuName = ++colNumber;
					try {
						level2 = (String) verifyObj(
								rowCell.getCell((short) columnStuName), "s"); // level2
					} catch (Exception e) {
						break;
					}
					String level3 = null;
					colNumber++;
					int columnPwd = ++colNumber;
					try {
						level3 = (String) verifyObj(
								rowCell.getCell((short) columnPwd), "s"); // level3
					} catch (Exception e) {
						break;
					}
					
					String level4 = null;
					colNumber++;
					int level3Name = ++colNumber;
					try {
						level4 = (String) verifyObj(
								rowCell.getCell((short) level3Name), "s"); // level3
					} catch (Exception e) {
						break;
					}
					String[] levelName = new String[4];
					levelName[0]=level1;
					levelName[1]=level2;
					levelName[2]=level3;
					levelName[3]=level4;
					uploadBatchList.add(levelName);
				}
			}
		}
		return uploadBatchList;
	}

	public static Object verifyObj(HSSFCell cell, String typeFlag) {
		DecimalFormat df = new DecimalFormat("#");
		String value = "";
		try {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC:
				value = df.format(cell.getNumericCellValue()).trim();
				break;
			case HSSFCell.CELL_TYPE_STRING:
				value = cell.getStringCellValue().trim();
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				value = cell.getCellFormula().trim();
				break;
			default:
				if (typeFlag.equals("s")) {
					return "";
				} else {
					return new BigDecimal("0");
				}
			}
		} catch (NumberFormatException ne) {
			ne.printStackTrace();
			if (typeFlag.equals("s")) {
				return "";
			} else {
				return new BigDecimal("0");
			}
		}
		if (typeFlag.equals("s")) {
			return value;
		} else {
			return new BigDecimal(value);
		}
	}
}
